package com.zbcbms.dao.impl;

import com.zbcbms.common.DaoImplCommon;
import com.zbcbms.dao.BookDao;
import com.zbcbms.entity.Book;
import com.zbcbms.entity.chart.BookTypeChart;
import com.zbcbms.entity.vo.BookVo;
import com.zbcbms.util.JDBCUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author fengq
 */

public class BookDaoImpl implements BookDao {

    private Connection connection;

    @Override
    public Boolean insertOne(Book entity){
        connection= JDBCUtil.getConnection();
        String sql="INSERT INTO book  "+
                "(id,book_number,book_name,status,publishing_house,price,publishing_time,create_time,update_time,is_deleted,author,`type`)"+
                "value(?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, UUID.randomUUID().toString().substring(0,25));
            preparedStatement.setString(2,entity.getBookNumber());
            preparedStatement.setString(3,entity.getBookName());
            preparedStatement.setString(4,"空闲");
            preparedStatement.setString(5,entity.getPublishingHouse());
            preparedStatement.setBigDecimal(6,entity.getPrice());
            preparedStatement.setDate(7,new Date(entity.getPublishingTime().getTime()));
            preparedStatement.setDate(8,new Date(System.currentTimeMillis()));
            preparedStatement.setDate(9,new Date(System.currentTimeMillis()));
            preparedStatement.setInt(10,0);
            preparedStatement.setString(11,entity.getAuthor());
            preparedStatement.setString(12,entity.getType());
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
           JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public Boolean update(Book entity) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE  book  "+
                "SET book_number=?," +
                " book_name=?," +
                " status=?," +
                " publishing_house=?," +
                " price=?," +
                " publishing_time=?, " +
                " update_time=? ," +
                " author=?," +
                " `type`=? "+
                "where id =? " +
                "and is_deleted=0";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,entity.getBookNumber());
            preparedStatement.setString(2,entity.getBookName());
            preparedStatement.setString(3,entity.getStatus());
            preparedStatement.setString(4,entity.getPublishingHouse());
            preparedStatement.setBigDecimal(5,entity.getPrice());
            preparedStatement.setDate(6,new Date(entity.getPublishingTime().getTime()));
            preparedStatement.setDate(7,new Date(System.currentTimeMillis()));
            preparedStatement.setString(8,entity.getAuthor());
            preparedStatement.setString(9,entity.getType());
            preparedStatement.setString(10,entity.getId());
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public Boolean deleteById(String number) {
        connection= JDBCUtil.getConnection();
        String sql="update book set is_deleted= ? where book_number=? ";
        return DaoImplCommon.deleteByCondition(connection,sql,number,1);
    }

    @Override
    public int deleteByIds(List<String> numbers) {
        connection= JDBCUtil.getConnection();
        return DaoImplCommon.deleteByIds(connection,numbers,"book","book_Number");
    }

    @Override
    public long selectCount(String status) {
        connection=JDBCUtil.getConnection();
        return DaoImplCommon.selectCount(connection,"book","status",status);

    }

    @Override
    public Boolean pjySicsDeleteById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="delete from book where book_number=? and is_delete=1";
        return DaoImplCommon.physicalDeleteByCondition(connection,sql,id);
    }

    @Override
    public Boolean recoverById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="update book set is_deleted=0 where book_number=? and is_deleted=1";
        return DaoImplCommon.physicalDeleteByCondition(connection,sql,id);
    }

    @Override
    public BookVo selectOneById(String id) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        String sql="select Id,book_Number,book_Name,status,publishing_House,price,publishing_Time,author,`type` " +
                "from book where is_deleted=0 and book_id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            BookVo bookVo=null;
            while (resultSet.next()){
                bookVo=new BookVo();
                bookVo.setId(resultSet.getString("Id"));
                bookVo.setBookName(resultSet.getString("book_Name"));
                bookVo.setBookNumber(resultSet.getString("book_Number"));
                bookVo.setStatus(resultSet.getString("status"));
                bookVo.setPublishingHouse(resultSet.getString("publishing_House"));
                bookVo.setPrice(resultSet.getBigDecimal("price"));
                bookVo.setPublishingTime(resultSet.getDate("publishing_Time"));
                bookVo.setAuthor(resultSet.getString("author"));
                bookVo.setType(resultSet.getString("type"));
            }
            return bookVo;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
           JDBCUtil.closeResource(connection,preparedStatement);
        }

    }


    @Override
    public List<BookVo> selectAllBook() {
        connection=JDBCUtil.getConnection();
        String sql="select Id,book_Number,book_Name,status,publishing_House,price,publishing_Time,author,`type`  " +
                "from book where is_deleted=0  " +
                "order BY create_time desc ";
        return DaoImplCommon.selectAllBook(connection,sql);
    }

    @Override
    public Boolean updateStatusByNumber(String number,String status) {
        connection=JDBCUtil.getConnection();
        String sql="update book set status=? where book_number=? and is_deleted=0";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,status);
            preparedStatement.setString(2,number);
            return preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public String selectIdByNumber(String number) {
        connection=JDBCUtil.getConnection();
        String sql="select id from book where book_number=? and is_deleted=0";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            ResultSet resultSet = preparedStatement.executeQuery();
            String id=null;
            while (resultSet.next()){
                id=resultSet.getString("id");
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<String> selectAllBookNumberWithStatus() {
        connection=JDBCUtil.getConnection();
        String sql="select book_number from book where  is_deleted=0 and status='空闲' ";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement= connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<String> list=new ArrayList<>();
            while (resultSet.next()){
                list.add(resultSet.getString("book_number"));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<String> selectNumberByKeywordWithStatus(String keyword) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            if (keyword!=""||keyword!=null) {
                String sql = "select book_number from book where  is_deleted=0 and book_number like ?  and status='空闲' ";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, "%" + keyword + "%");
                resultSet = preparedStatement.executeQuery();
            }else {
                String sql = "select book_number from book where  is_deleted=0 and status='空闲'";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
            }
            List<String> list=new ArrayList<>();
            while (resultSet.next()){
                list.add(resultSet.getString("book_number"));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public String selectBookNameByNumber(String number) {
        connection=JDBCUtil.getConnection();
        String sql="select book_name from book where book_number=? and is_deleted=0";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            ResultSet resultSet = preparedStatement.executeQuery();
            String id=null;
            while (resultSet.next()){
                id=resultSet.getString("book_name");
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<BookTypeChart> selectBookCountByType() {
        connection=JDBCUtil.getConnection();
        String sql="select Count(*) as nums,`type` from book where is_deleted=0 " +
                " GROUP BY `type`";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement= connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookTypeChart> list=new ArrayList<>();
            while (resultSet.next()){
                BookTypeChart bookTypeChart = new BookTypeChart();
                bookTypeChart.setNums(resultSet.getLong("nums"));
                bookTypeChart.setType(resultSet.getString("type"));
                list.add(bookTypeChart);;
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<BookVo> selectAllDeletedBook() {
        connection=JDBCUtil.getConnection();
        String sql="select Id,book_Number,book_Name,status,publishing_House,price,publishing_Time,author,`type`  " +
                "from book where is_deleted=1  " +
                "order BY create_time desc ";
        return DaoImplCommon.selectAllBook(connection,sql);
    }

    @Override
    public List<BookVo> selectBookVoByKeWord(String keyword) {
        connection=JDBCUtil.getConnection();
        String sql="select Id,book_Number,book_Name,status,publishing_House,price,publishing_Time,author,`type`  \n" +
                "                from book \n" +
                "\t\t\t\t\t\t\t\twhere is_deleted=0 \n" +
                "\t\t\t\t\t\t\t\tand CONCAT(book_number,book_name,status,author) like ? \n" +
                "                order BY create_time desc ";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"%"+keyword+"%");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookVo> books=new ArrayList<>();
            while(resultSet.next()){
                BookVo bookVo=new BookVo();
                bookVo.setId(resultSet.getString("id"));
                bookVo.setBookName(resultSet.getString("book_Name"));
                bookVo.setBookNumber(resultSet.getString("book_Number"));
                bookVo.setStatus(resultSet.getString("status"));
                bookVo.setPublishingHouse(resultSet.getString("publishing_House"));
                bookVo.setPrice(resultSet.getBigDecimal("price"));
                bookVo.setPublishingTime(resultSet.getDate("publishing_Time"));
                bookVo.setAuthor(resultSet.getString("author"));
                bookVo.setType(resultSet.getString("type"));
                books.add(bookVo);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

}
